﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Maticsoft.DBUtility;
using System.Data;
using WLong.Model;
using System.Collections;
using System.Data.SqlClient;

namespace DAL.wlDAL
{
    public class Article
    {
        /// <summary>
        /// 根据套餐组获得信息
        /// </summary>
        /// <param name="Name">string</param>
        /// <returns>DataTable</returns>
        public DataTable GetTaoCanInfo(string Name)
        {
            string sql = @" select Fct_OtherCategory.*,Rel_ArticleOtherCategory.ArticleInfomationID from Rel_ArticleOtherCategory
                         inner join Fct_OtherCategory on Rel_ArticleOtherCategory.OtherCategoryID=Fct_OtherCategory.OtherCategoryID
                         and Fct_OtherCategory.Disabled=0 and Fct_OtherCategory.OtherCategoryGroup=@Name ";
            sql = sql.Replace("@Name", "'" + Name + "'");
            DataTable dttaocan = DbHelperSQL.Query(sql).Tables[0];
            return dttaocan;
        }
        /// <summary>
        /// 根据文章ID获得信息
        /// </summary>
        /// <param name="Name">string</param>
        /// <returns>DataTable</returns>
        public DataTable GetTaoCanInfoContent(string ArticleID)
        {
            string sql = @" select Fct_ArticleInfomation.*,Fct_OtherCategory.OtherCategoryName,Fct_OtherCategory.OtherCategoryGroup from Rel_ArticleOtherCategory
                         inner join Fct_OtherCategory on Rel_ArticleOtherCategory.OtherCategoryID=Fct_OtherCategory.OtherCategoryID
                         and Fct_OtherCategory.Disabled=0 
                        inner join Fct_ArticleInfomation on Fct_ArticleInfomation.ArticleID=Rel_ArticleOtherCategory.ArticleInfomationID
                         and Fct_ArticleInfomation.Disabled=0 ";
            sql += " where Fct_ArticleInfomation.ArticleID='" + ArticleID + "' ";
            DataTable dttaocan = DbHelperSQL.Query(sql).Tables[0];
            return dttaocan;
        }

        /// <summary>
        /// 添加修改
        /// </summary>
        /// <param name="article"></param>
        /// <param name="articleCategory"></param>
        /// <param name="isAdd"></param>
        /// <returns></returns>
        public int AddArticle(ArticleInfomation article, Rel_ArticleOtherCategory articleCategory, bool isAdd)
        {
            List<CommandInfo> sqlList = new List<CommandInfo>();
            string sql1=null;
            string sql2=null;
            if (isAdd)
            {
                sql1 = @"insert into Fct_ArticleInfomation (ArticleID,ArticleTitle,StartTime,EndTime,ArticleContent,Disabled,REC_CreateTime,REC_CreateBy) 
                values (@ArticleID,@ArticleTitle,@StartTime,@EndTime,@ArticleContent,@Disabled,@REC_CreateTime,@REC_CreateBy)";
                SqlParameter[] para = new SqlParameter[8];
                para[0] =new SqlParameter ("@ArticleID",article.ArticleID);
                para[1] = new SqlParameter ("@ArticleTitle",article.ArticleTitle);
                para[2] =new SqlParameter("@StartTime",article.StartTime);
                para[3] =new SqlParameter("@EndTime",article.EndTime);
                para[4] = new SqlParameter ("@ArticleContent",article.ArticleContent);
                para[5]=new SqlParameter ("@Disabled",article.Disabled);
                para[6] =new SqlParameter ("@REC_CreateTime",article.REC_CreateTime);
                para[7]=new SqlParameter ("@REC_CreateBy",article.REC_CreateBy);

                sql2 = @"insert into Rel_ArticleOtherCategory (ArticleOtherCategoryID,OtherCategoryID,ArticleInfomationID,Sort,Disabled,REC_CreateTime,REC_CreateBy) 
                values (@ArticleOtherCategoryID,@OtherCategoryID,@ArticleInfomationID,@Sort,@Disabled,@REC_CreateTime,@REC_CreateBy)";
                SqlParameter[] para2 = new SqlParameter[7];
                para2[0] = new SqlParameter("@ArticleOtherCategoryID", articleCategory.ArticleOtherCategoryID);
                para2[1] = new SqlParameter("@OtherCategoryID", articleCategory.OtherCategoryID);
                para2[2] = new SqlParameter("@ArticleInfomationID", articleCategory.ArticleInfomationID);
                para2[3] = new SqlParameter("@Sort", articleCategory.Sort);
                para2[4] = new SqlParameter("@Disabled", articleCategory.Disabled);
                para2[5] = new SqlParameter("@REC_CreateTime", articleCategory.REC_CreateTime);
                para2[6] = new SqlParameter("@REC_CreateBy", articleCategory.REC_CreateBy);

                CommandInfo c1 = new CommandInfo(sql1, para);
                CommandInfo c2 = new CommandInfo(sql2, para2);
                sqlList.Add(c1);
                sqlList.Add(c2);
            }
            else
            {
                sql1 = @"update Fct_ArticleInfomation set ArticleTitle=@ArticleTitle,StartTime=@StartTime,EndTime=@EndTime,ArticleContent=@ArticleContent,REC_ModifyTime=@REC_ModifyTime,REC_ModifyBy=@REC_ModifyBy where ArticleID=@ArticleID";
                SqlParameter[] para = new SqlParameter[7];
                para[0] = new SqlParameter("@ArticleTitle", article.ArticleTitle);
                para[1] = new SqlParameter("@StartTime", article.StartTime);
                para[2] = new SqlParameter("@EndTime", article.EndTime);
                para[3] = new SqlParameter("@ArticleContent", article.ArticleContent);
                para[4] = new SqlParameter("@REC_ModifyTime", article.REC_ModifyTime);
                para[5] = new SqlParameter("@REC_ModifyBy", article.REC_ModifyBy);
                para[6] = new SqlParameter("@ArticleID", article.ArticleID);

                sql2 = @"update Rel_ArticleOtherCategory set OtherCategoryID=@OtherCategoryID,ArticleInfomationID=@ArticleInfomationID,Sort=@Sort,REC_ModifyTime=@REC_ModifyTime,REC_ModifyBy=@REC_ModifyBy where ArticleOtherCategoryID=@ArticleOtherCategoryID";
                SqlParameter[] para2 = new SqlParameter[6];
                para2[0] = new SqlParameter("@OtherCategoryID", articleCategory.OtherCategoryID);
                para2[1] = new SqlParameter("@ArticleInfomationID", articleCategory.ArticleInfomationID);
                para2[2] = new SqlParameter("@Sort", articleCategory.Sort);
                para2[3] = new SqlParameter("@REC_ModifyTime", articleCategory.REC_ModifyTime);
                para2[4] = new SqlParameter("@REC_ModifyBy", articleCategory.REC_ModifyBy);
                para2[5] = new SqlParameter("@ArticleOtherCategoryID", articleCategory.ArticleOtherCategoryID);

                CommandInfo c1 = new CommandInfo(sql1, para);
                CommandInfo c2 = new CommandInfo(sql2, para2);
                sqlList.Add(c1);
                sqlList.Add(c2);
            }
            return DbHelperSQL.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 返回所有文章
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllArticle()
        {
            string sql = string.Format(@"select * from Fct_ArticleInfomation fa 
            left join Rel_ArticleOtherCategory ra on fa.ArticleID=ra.ArticleInfomationID 
            where fa.Disabled=0 and ra.Disabled=0 ");
            return DbHelperSQL.Query(sql).Tables[0];
        }
        /// <summary>
        /// 根据组别获得类型
        /// </summary>
        /// <param name="IT">string</param>
        /// <returns>DataTable</returns>
        public DataTable GetSuportCategory(string Group)
        {
            string sql = @" select Fct_OtherCategory.OtherCategoryID 
	                        ,Fct_OtherCategory.OtherCategoryCode,Fct_OtherCategory.OtherCategoryName,Fct_OtherCategory.OtherCategoryGroup
                            from Fct_OtherCategory
                            where Fct_OtherCategory.Disabled=0 and Fct_OtherCategory.OtherCategoryGroup=";
            sql += "'" + Group + "' order by Fct_OtherCategory.Sort asc";
            DataTable dttaocan = DbHelperSQL.Query(sql).Tables[0];
            return dttaocan;
        }
        /// <summary>
        /// 根据特定条件查信息OtherCategoryGroup或OtherCategoryID
        /// </summary>
        /// <param name="htwhere">Hashtable</param>
        /// <returns>DataTable</returns>
        public DataTable GetActicleInfo(Hashtable htwhere)
        {
            string sql = @" select Fct_ArticleInfomation.*,Fct_OtherCategory.OtherCategoryName,Fct_OtherCategory.OtherCategoryGroup,Fct_OtherCategory.OtherCategoryID,Fct_ArticleInfomation.REC_CreateTime  
                            from Fct_OtherCategory
                                inner join Rel_ArticleOtherCategory on Fct_OtherCategory.OtherCategoryID=Rel_ArticleOtherCategory.OtherCategoryID and Rel_ArticleOtherCategory.Disabled=0
                                inner join Fct_ArticleInfomation on Rel_ArticleOtherCategory.ArticleInfomationID=Fct_ArticleInfomation.ArticleID and Fct_ArticleInfomation.Disabled=0 
                                where Fct_OtherCategory.Disabled=0 ";
            if (htwhere.ContainsKey("OtherCategoryGroup"))
            {
                sql += " and Fct_OtherCategory.OtherCategoryGroup='" + htwhere["OtherCategoryGroup"].ToString() + "' ";
            }
            if (htwhere.ContainsKey("OtherCategoryID"))
            {
                sql += " and Fct_OtherCategory.OtherCategoryID='" + htwhere["OtherCategoryID"].ToString() + "' ";
            }
            sql += " order by Fct_ArticleInfomation.REC_CreateTime desc ";
            DataTable dttaocan = DbHelperSQL.Query(sql).Tables[0];
            return dttaocan;
            //and Fct_OtherCategory.OtherCategoryGroup='IT'
            // order by Fct_OtherCategory.Sort asc";
        }
    }
}
